﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using HCQ2_Common.SQL;
using HCQ2_IDAL;
using HCQ2_Model;
using HCQ2_Model.ClassRoom;
using HCQ2_Model.ViewModel;

namespace HCQ2_DAL_MSSQL
{
    /// <summary>
    /// 直播课堂
    /// </summary>
    public partial class N_VRLineClassRoomDAL : IN_VRLineClassRoomDAL
    {
        /// <summary>
        /// 检查老师新建直播课堂上课时间和已存在的直播课堂的上课时间是否存在重叠
        /// </summary>
        /// <param name="UserId">用户ID</param>
        /// <param name="StartTime">上课时间</param>
        /// <param name="EndTime">上课结束时间</param>
        /// <returns>有数据表示存在重叠</returns>
        public List<CheckClassRoomTimeOverlap> CheckClassRoomTimeOverlap(int UserId, DateTime StartTime, DateTime EndTime)
        {
            string sql = $"select RmId,CrName,StartTime,StartLen,EndTime from N_VRLineClassRoom where UserId ={UserId} and '{StartTime.ToString("yyyy-MM-dd HH:mm")}' < EndTime and '{EndTime.ToString("yyyy-MM-dd HH:mm")}' > StartTime";

            var dbResult = SqlHelper.ExecuteList<CheckClassRoomTimeOverlap>(sql);

            return dbResult;
        }

        /// <summary>
        /// 获取直播课堂列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public TableModel<List<ClassRoomQueryResultDto>> List(ClassRoomQueryDto query)
        {            
            string sql = $@"select
            a.RmId,
            a.UserId,
            a.FolderId,
            b.folder_url FolderImgUrl,
            a.CrName,
            SUBSTRING(CONVERT(varchar(20),a.StartTime,120),0,17) StartTime,
            a.StartLen,
            a.Scope,
            a.Note,
            a.Price,
            a.IfFinish,
            a.VideoUrl,
            a.CreateName,
            b.folder_path FolderPath,
            (select COUNT(UserId) from N_VRUserAttend where RmId=a.RmId)UserAttendNum,
            a.PersonNumber,
            a.Device,
            u.user_img CreateNamePhotoUrl
            from N_VRLineClassRoom a
            inner join T_DocumentFolder b on a.FolderId=b.folder_id
            left join T_User u on a.CreateId=u.user_id
            { GetListWhere(query) }";

            List<ClassRoomQueryResultDto> results = SqlHelper.ExecutePageList<ClassRoomQueryResultDto>(query.page, query.rows, "RmId", sql,false);
            int count = SqlHelper.GetCount(sql);

            if (results.Count == 0) {
                return null;
            }

            List<string> RmIds = new List<string>();
            results.ForEach((r) => {
                RmIds.Add($"{r.RmId.Value}");                
            });

            sql = $@"select 
            a.RmId,
            a.FileId,
            b.file_name FileName,
            c.file_url FileImgUrl,
            b.file_note FileNode,
            case when b.down_number>0 then CONVERT(bit,1) else CONVERT(bit,0) end IsDown,
            (select CONVERT(nvarchar,MAX(option_size)) from T_DocFileOptions where file_id=a.FileId)+ 'MB' FileSize,
            a.RfId Num,
            a.IfDefault
            from N_VRLineClassRoomFile a
            inner join T_DocumentInfo b on a.FileId=b.file_id
            inner join (select * from (select ROW_NUMBER()over(partition by file_id order by create_date desc)rn,* from T_DocBagFileRecord)t where rn=1
            )c on a.FileId = c.file_id
            where a.RmId in ({string.Join(",", RmIds)})";

            List<ClassRoomFileQueryResultDtoDB> resultDb = SqlHelper.ExecuteList<ClassRoomFileQueryResultDtoDB>(sql);

            if (resultDb != null && resultDb.Count > 0)
            {
                results.ForEach((r) =>
                {
                    r.RoomFiles.AddRange(resultDb.Where(w => w.RmId == r.RmId));
                });
            }

            return  new TableModel<List<ClassRoomQueryResultDto>>() { 
                rows = results,
                total = count
            };
        }

        /// <summary>
        /// 获取上课人数
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public int GetClassRoomPersonNumber(GetClassRoomPersonNumberQueryDto dto)
        {
            string sql = $@"select COUNT(UserId) from N_VRUserAttend where RmId in (
	            select RmId from N_VRLineClassRoom {GetGetClassRoomPersonNumberWhere(dto)}
            )";

            object val = SqlHelper.ExecuteScalar(sql);

            return Convert.ToInt32(val);
        }

        /// <summary>
        /// 获取直播课堂数
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public int GetClassRoomNumber(GetClassRoomNumberQueryDto dto)
        {
            string sql = $"select COUNT(RmId) from N_VRLineClassRoom {GetClassRoomNumberWhere(dto)}";

            object val = SqlHelper.ExecuteScalar(sql);

            return Convert.ToInt32(val);
        }

        #region 私有方法

        /// <summary>
        /// 构建获取上课人数where条件
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        private string GetGetClassRoomPersonNumberWhere(GetClassRoomPersonNumberQueryDto query) 
        {
            List<string> where = new List<string>();

            if (query.StartTime != null)
            {
                where.Add($"StartTime>='{query.StartTime.Value.ToString("yyyy-MM-dd HH:ss")}'");
            }
            if (query.EndTime != null)
            {
                where.Add($"StartTime<='{query.EndTime.Value.ToString("yyyy-MM-dd HH:ss")}'");
            }
            if (query.IfFinish != null)
            {
                int i = query.IfFinish.Value ? 1 : 0;
                where.Add($"IfFinish={i}");
            }
            string w = string.Empty;
            if (where.Count > 0)
            {
                w = $" where {string.Join(" and ", where)}";
            }

            return w;

        }

        /// <summary>
        /// 构建获取直播课堂数where条件
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        private string GetClassRoomNumberWhere(GetClassRoomNumberQueryDto dto)
        {
            //直接调用方法，是因为参数一样
            return GetGetClassRoomPersonNumberWhere(dto);            
        }

        /// <summary>
        /// 构建Where条件
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        private string GetListWhere(ClassRoomQueryDto query)
        {
            List<string> where = new List<string>();

            if (!string.IsNullOrEmpty(query.CreateUserId))
            {            
                where.Add($"a.UserId={query.CreateUserId}");
            }
            if (query.FolderId != null)
            {
                where.Add($"a.FolderId={query.FolderId}");
            }
            if (!string.IsNullOrEmpty(query.KeyWork))
            {
                where.Add($"(a.CrName like '%{query.KeyWork}%' or a.Note like '%{query.KeyWork}%')");
            }

            //开始结束时间都不为空
            if (query.StartTime != null && query.EndTime != null) {
                where.Add($"CONVERT(date,a.StartTime) between '{query.StartTime.Value.ToString("yyyy-MM-dd")}' and '{query.EndTime.Value.ToString("yyyy-MM-dd")}'");
            }
            //开始时间不为空，结束时间为空
            if (query.StartTime != null && query.EndTime == null) {
                where.Add($"CONVERT(date,a.StartTime)>='{query.StartTime.Value.ToString("yyyy-MM-dd")}'");
            }

            //开始时间为空，结束时间不为空
            if (query.StartTime == null && query.EndTime != null)
            {
                where.Add($"CONVERT(date,a.StartTime)<='{query.EndTime.Value.ToString("yyyy-MM-dd")}'");
            }

            if (query.IfFinish != null)
            {
                int i = query.IfFinish.Value ? 1 : 0;
                where.Add($"a.IfFinish={i}");
            }
            string w = string.Empty;
            if (where.Count > 0)
            {
                w = $" where {string.Join(" and ", where)}";
            }

            return w;
        }

        #endregion
    }
}
